﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Configuration;
using ConvertDB;
using System.Text;
using System.Data.OleDb;

namespace WebServiceSmartphone
{
    /// <summary>
    /// Summary description for ReloadDataSmartPhone
    /// </summary>
    /// 

    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class ReloadDataSmartPhone : System.Web.Services.WebService
    {
        private string GetDatabaseName(string sitecode, DAL dal)
        {
            string sql = "", err = "", DBName = "";
            sql = "SELECT     DBName FROM  Distributor WHERE     (Sitecode = '" + sitecode + "')";
            DBName = dal.MyExecuteReaders(ref err, sql, CommandType.Text);
            //return "SMART00001";
            return DBName;
        }
        private string GetServerName(string sitecode, DAL dal)
        {
            string sql = "", err = "", ServerName = "";
            sql = "SELECT     ServerName FROM  Distributor WHERE     (Sitecode = '" + sitecode + "')";
            ServerName = dal.MyExecuteReaders(ref err, sql, CommandType.Text);
            return ServerName;
        }
        public string convertToUnSign2(string s)
        {
            string stFormD = s.Normalize(NormalizationForm.FormD);
            StringBuilder sb = new StringBuilder();
            for (int ich = 0; ich < stFormD.Length; ich++)
            {
                System.Globalization.UnicodeCategory uc = System.Globalization.CharUnicodeInfo.GetUnicodeCategory(stFormD[ich]);
                if (uc != System.Globalization.UnicodeCategory.NonSpacingMark)
                {
                    sb.Append(stFormD[ich]);
                }
            }
            sb = sb.Replace('Đ', 'D');
            sb = sb.Replace('đ', 'd');
            return (sb.ToString().Normalize(NormalizationForm.FormD));
        }
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
        [WebMethod]
        public string SendMassage(string send, string Recive, string Body, string sitecode)
        {
            string date = DateTime.Now.ToString("dd/MM/yyyy");
            string sql = "";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            sql = "insert into " + DBName + ".dbo.MessageInbox(Sender, Recived, Body, timing, messagedate, status)values('" + send + "','" + Recive + "','" + Body + "',Getdate(),'09/10/2012',0)";
            bool fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
            if (fl)
                return "1";
            else
                return "0";
        }
        [WebMethod]
        public DataSet GetReportType(string sitecode)
        {
            DataSet ds = new DataSet();
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string sql = "SELECT     ID, ReportTypeName FROM  " + DBName + ".dbo.DailySalesReportType where ID in(SELECT     ReportTypeKey FROM  " + DBName + ".dbo.DailySalesReportHeader WHERE     (FromDate < GETDATE()) AND (ToDate > GETDATE()))";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetReportHeader(string date, string sitecode)
        {
            DataSet ds = new DataSet();
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string sql = "SELECT     ID, ReportTopic, IsValue, IsPicture, ReportTypeKey, ReportTypeId FROM " + DBName + ".dbo.DailySalesReportHeader WHERE     (FromDate < GETDATE()) AND (ToDate > GETDATE())";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetCustomer(string ID, string usertype, string sitecode, string type)
        {
            DataSet ds = new DataSet();
            string error = "";
            string sql = "sp_GetCustomer";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);

            ds = dal.MyExecuteQuery(ref error, sql, CommandType.StoredProcedure,
                new SqlParameter("@key", ID),
                new SqlParameter("@usertype", usertype),
                new SqlParameter("@DBName", DBName),
                new SqlParameter("@type", type));
            return ds;
        }
        [WebMethod]
        public DataSet GetARCUST(string usercode, string usertype, string sitecode, string type)
        {
            DataSet ds = new DataSet();
            string error = "";
            string sql = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            sql = "SELECT     customer_key + '{' + customer_nameUNI + '{' + AddressUNI + '{' + StreetUNI + '{' + WardUNI + '{' + TownCode + '{' + ProvinceCode + '{' + salesp_key + '{'+ ShopOwnerUNI AS customer, GPSV, GPSH FROM  " + DBName + ".dbo.ARCUST WHERE     (NOT (customer_key IN (SELECTcustomerkey  FROM  " + DBName + ".dbo.TCARCUST)))";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string InsertCustomerkey(string customerkey, string Sitecode, string Customername, string shopowner, string address,
            string street, string ward, string towncode, string provincecode, string DeliveryAddress, string phone,
            string T, string TradeSegment, string SubTradeSegment, string Otype, string VLevel, string StatusType, string salekey, string Lat, string Long)
        {
            string error = "";
            string cuskey = "";
            DAL dal = new DAL();
            string CustomernameUNI = Customername;
            string addressUNI = address;
            string shopownerUNI = shopowner;
            string wardUNI = ward;
            string streetUNI = street;
            string DeliveryAddressUNI = DeliveryAddress;
            ConvertFont convert = new ConvertFont();
            convert.Convert(ref Customername, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref address, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref shopowner, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref ward, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref street, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref DeliveryAddress, FontIndex.iUNI, FontIndex.iVNI);
            string findfield = convertToUnSign2(Customername + DeliveryAddress);
            string DBName = GetDatabaseName(Sitecode, dal);
            string sql = "";
            if (customerkey == "0")
            {
                sql = "SELECT     TOP (1) customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE   (ISNUMERIC(customerkey) = 1) ORDER BY LEN(customerkey) DESC, customerkey DESC";
                cuskey = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                sql = "SELECT     TOP (1) customerkey FROM  " + DBName + ".dbo.TCARCUSTH WHERE   (ISNUMERIC(customerkey) = 1) ORDER BY LEN(customerkey) DESC, customerkey DESC";
                string cuskeyH = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                if (cuskey == "" && error == "")
                    cuskey = "0";
                if (cuskeyH == "" && error == "")
                    cuskeyH = "0";
                if (int.Parse(cuskey) < int.Parse(cuskeyH))
                    cuskey = cuskeyH;
                cuskey = (int.Parse(cuskey) + 1).ToString();
            }
            else
            {
                cuskey = customerkey;
            }
            sql = "Insert into " + DBName + ".dbo.TCARCUST(customerkey, customername, address,customernameUNI, addressUNI, Tel, [credit limit], terms, salepersonkey, ProvinceCode, TownCode, MCP2, MCP3, MCP4, MCP5, MCP6, MCP7, " +
                       "   Active, Vol, TradeSegment, Freq, Otype, Repeat, Manual, AvgOff, TopOff, BizControl, Chanel, VanDSR, location_Key, RSUCust, RSUKey, " +
                       "   Arsalepersonkey, DeliveryAddress,DeliveryAddressUNI, TeamID, isShow, Lat, Long, Ward,WardUNI, Street,StreetUNI, RecTime, RecUser, ShopOwner,ShopOwnerUNI, sitecode,SubTradeSegment,VLevel,StatusType,FindField)" +
                       " values('" + cuskey + "','" + Customername + "','" + address + "',N'" + CustomernameUNI + "',N'" + addressUNI + "','" + phone + "',500000,7,'" + salekey + "','" + provincecode + "','" + towncode + "',0,0,0,0,0,0" +
                       ",0,0,'" + TradeSegment + "',4,'',0,0,0,0,0,1,0,'WH001',0,0,'" + salekey + "','" + DeliveryAddress + "',N'" + DeliveryAddressUNI + "',1,0," + Lat + "," + Long + ",'" + ward + "',N'" + wardUNI + "','" + street + "',N'" + streetUNI + "',getdate(),'" + salekey + "','" + shopowner + "',N'" + shopownerUNI + "','" + Sitecode + "','" + SubTradeSegment + "','" + VLevel + "','" + StatusType + "',N'" + findfield + "' )";
            bool kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            if (customerkey != "0" && kq == true)
            {
                sql = "delete " + DBName + ".dbo.TCARCUSTH where customerkey='" + customerkey + "'";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            }
            if (T == "2")
            {
                sql = "update " + DBName + ".dbo.TCARCUST set MCP2=1 where customerkey='" + cuskey + "'";
            }
            if (T == "3")
            {
                sql = "update " + DBName + ".dbo.TCARCUST set MCP3=1 where customerkey='" + cuskey + "'";
            }
            if (T == "4")
            {
                sql = "update " + DBName + ".dbo.TCARCUST set MCP4=1 where customerkey='" + cuskey + "'";
            }
            if (T == "5")
            {
                sql = "update " + DBName + ".dbo.TCARCUST set MCP5=1 where customerkey='" + cuskey + "'";
            }
            if (T == "6")
            {
                sql = "update " + DBName + ".dbo.TCARCUST set MCP6=1 where customerkey='" + cuskey + "'";
            }
            if (T == "7")
            {
                sql = "update " + DBName + ".dbo.TCARCUST set MCP7=1 where customerkey='" + cuskey + "'";
            }
            if (kq)
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            if (kq)
                return cuskey;
            else
                return "0";
        }
        [WebMethod]
        public string InsertCustomer(string customerkey, string Sitecode, string Customer, string phone, string T, string CustType, string salekey, string Lat, string Long)
        {
            string[] cust = Customer.Split(';');
            string Customername = cust[0];
            string shopowner = cust[1];
            string address = cust[2];
            string street = cust[3];
            string ward = cust[4];
            string towncode = cust[5];
            string provincecode = cust[6];
            string DeliveryAddress = cust[0] + " " + cust[1] + " " + cust[2] + " " + cust[3] + " " + cust[4] + " " + cust[5] + " " + cust[6];
            string[] type = CustType.Split(';');

            string TradeSegment = type[0];
            string SubTradeSegment = type[1];
            string Otype = type[2];
            string VLevel = type[3];
            string StatusType = type[4];

            string error = "";
            string cuskey = "";
            DAL dal = new DAL();
            string CustomernameUNI = Customername;
            string addressUNI = address;
            string shopownerUNI = shopowner;
            string wardUNI = ward;
            string streetUNI = street;
            string DeliveryAddressUNI = DeliveryAddress;
            ConvertFont convert = new ConvertFont();
            convert.Convert(ref Customername, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref address, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref shopowner, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref ward, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref street, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref DeliveryAddress, FontIndex.iUNI, FontIndex.iVNI);
            string findfield = convertToUnSign2(Customername + DeliveryAddress);
            string DBName = GetDatabaseName(Sitecode, dal);
            string sql = "";
            bool kq = false;
            if (customerkey == "0")
            {
                sql = "SELECT     TOP (1) customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE   (ISNUMERIC(customerkey) = 1) ORDER BY LEN(customerkey) DESC, customerkey DESC";
                cuskey = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                sql = "SELECT     TOP (1) customer_key FROM  " + DBName + ".dbo.ARCUST WHERE   (ISNUMERIC(customer_key) = 1) ORDER BY LEN(customer_key) DESC, customer_key DESC";
                string cuskeyH = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                if (cuskey == "" && error == "")
                    cuskey = "0";
                if (cuskeyH == "" && error == "")
                    cuskeyH = "0";
                if (int.Parse(cuskey) < int.Parse(cuskeyH))
                    cuskey = cuskeyH;
                cuskey = (int.Parse(cuskey) + 1).ToString();
                sql = "Insert into " + DBName + ".dbo.ARCUST(customer_key, customer_name, address, phone, ProvinceCode, TownCode, Active, Recdate, Rectime, ShopOwner, Street, Ward, AddressUNI, customer_nameUNI, ShopOwnerUNI, " +
                      " StreetUNI, WardUNI, FindField, Lat, Long, IsApproval, ApprovalGPS)" +
                      " values('" + cuskey + "','" + Customername + "','" + address + "','" + phone + "','" + provincecode + "','" + towncode + "',1,Getdate(),Getdate(),'" + shopowner + "','" + street + "','" + ward + "',N'" + addressUNI + "',N'" + CustomernameUNI + "',N'" + shopownerUNI + "'," +
                      "N'" + streetUNI + "',N'" + wardUNI + "',N'" + findfield + "'," + Lat + "," + Long + ",0,0)";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                sql = "insert into " + DBName + ".dbo.TCARCUSTH(customerkey, customername, address,customernameUNI, addressUNI, Tel, [credit limit], terms, salepersonkey, ProvinceCode, TownCode, MCP2, MCP3, MCP4, MCP5, MCP6, MCP7, " +
                       "   Active, Vol, TradeSegment, Freq, Otype, Repeat, Manual, AvgOff, TopOff, BizControl, Chanel, VanDSR, location_Key, RSUCust, RSUKey, " +
                       "   Arsalepersonkey, DeliveryAddress,DeliveryAddressUNI, TeamID, isShow, Lat, Long, Ward,WardUNI, Street,StreetUNI, RecTime, RecUser, ShopOwner,ShopOwnerUNI, sitecode,SubTradeSegment,VLevel,StatusType,FindField)" +
                       "Select customerkey, customername, address,customernameUNI, addressUNI, Tel, [credit limit], terms, salepersonkey, ProvinceCode, TownCode, MCP2, MCP3, MCP4, MCP5, MCP6, MCP7, " +
                       "   Active, Vol, TradeSegment, Freq, Otype, Repeat, Manual, AvgOff, TopOff, BizControl, Chanel, VanDSR, location_Key, RSUCust, RSUKey, " +
                       "   Arsalepersonkey, DeliveryAddress,DeliveryAddressUNI, TeamID, isShow, Lat, Long, Ward,WardUNI, Street,StreetUNI, RecTime, RecUser, ShopOwner,ShopOwnerUNI, sitecode,SubTradeSegment,VLevel,StatusType,FindField " +
                       " from " + DBName + ".dbo.TCARCUST where customerkey='" + cuskey + "'";
                //kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);

                sql = "delete " + DBName + ".dbo.TCARCUST where customerkey='" + cuskey + "'";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                sql = "Insert into " + DBName + ".dbo.TCARCUST(customerkey, customername, address,customernameUNI, addressUNI, Tel, [credit limit], terms, salepersonkey, ProvinceCode, TownCode, MCP2, MCP3, MCP4, MCP5, MCP6, MCP7, " +
                           "   Active, Vol, TradeSegment, Freq, Otype, Repeat, Manual, AvgOff, TopOff, BizControl, Chanel, VanDSR, location_Key, RSUCust, RSUKey, " +
                           "   Arsalepersonkey, DeliveryAddress,DeliveryAddressUNI, TeamID, isShow, Lat, Long, Ward,WardUNI, Street,StreetUNI, RecTime, RecUser, ShopOwner,ShopOwnerUNI, sitecode,SubTradeSegment,VLevel,StatusType,FindField)" +
                           " values('" + cuskey + "','" + Customername + "','" + address + "',N'" + CustomernameUNI + "',N'" + addressUNI + "','" + phone + "',500000,7,'" + salekey + "','" + provincecode + "','" + towncode + "',0,0,0,0,0,0" +
                           ",0,0,'" + TradeSegment + "',4,'',0,0,0,0,0,1,0,'WH001',0,0,'" + salekey + "','" + DeliveryAddress + "',N'" + DeliveryAddressUNI + "',1,1," + Lat + "," + Long + ",'" + ward + "',N'" + wardUNI + "','" + street + "',N'" + streetUNI + "',getdate(),'" + salekey + "','" + shopowner + "',N'" + shopownerUNI + "','" + Sitecode + "','" + SubTradeSegment + "','" + VLevel + "','" + StatusType + "',N'" + findfield + "' )";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                sql = "delete " + DBName + ".dbo.TCARCUSTH where customerkey='" + cuskey + "'";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);

                if (customerkey != "0" && kq == true)
                {
                    sql = "delete " + DBName + ".dbo.TCARCUSTH where customerkey='" + customerkey + "'";
                    kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                }
                if (T == "2")
                {
                    sql = "update " + DBName + ".dbo.TCARCUST set MCP2=1 where customerkey='" + cuskey + "'";
                }
                if (T == "3")
                {
                    sql = "update " + DBName + ".dbo.TCARCUST set MCP3=1 where customerkey='" + cuskey + "'";
                }
                if (T == "4")
                {
                    sql = "update " + DBName + ".dbo.TCARCUST set MCP4=1 where customerkey='" + cuskey + "'";
                }
                if (T == "5")
                {
                    sql = "update " + DBName + ".dbo.TCARCUST set MCP5=1 where customerkey='" + cuskey + "'";
                }
                if (T == "6")
                {
                    sql = "update " + DBName + ".dbo.TCARCUST set MCP6=1 where customerkey='" + cuskey + "'";
                }
                if (T == "7")
                {
                    sql = "update " + DBName + ".dbo.TCARCUST set MCP7=1 where customerkey='" + cuskey + "'";
                }
            }
            else
            {
                cuskey = customerkey;
                sql = "delete " + DBName + ".dbo.ARCUST where customer_key='" + cuskey + "'";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                sql = "Insert into " + DBName + ".dbo.ARCUST(customer_key, customer_name, address, phone, ProvinceCode, TownCode, Active, Recdate, Rectime, ShopOwner, Street, Ward, AddressUNI, customer_nameUNI, ShopOwnerUNI, " +
                     " StreetUNI, WardUNI, FindField, Lat, Long, IsApproval, ApprovalGPS)" +
                     " values('" + cuskey + "','" + Customername + "','" + address + "','" + phone + "','" + provincecode + "','" + towncode + "',1,Getdate(),Getdate(),'" + shopowner + "','" + street + "','" + ward + "',N'" + addressUNI + "',N'" + CustomernameUNI + "',N'" + shopownerUNI + "'," +
                     "N'" + streetUNI + "',N'" + wardUNI + "',N'" + findfield + "'," + Lat + "," + Long + ",0,0)";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                sql = "Update TCARCUST set customer_name='" + Customername + "', address='" + address + "', ProvinceCode='" + provincecode + "', TownCode='" + towncode + "', ShopOwner='" + shopowner + "', Street='" + street + "', Ward='" + ward + "' where customer_key='" + cuskey + "'";

            }

            if (kq)
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            if (kq)
                return cuskey;
            else
                return "0";
        }
        [WebMethod]
        public DataSet GetPickinglist(string RSUkey, string sitecode)
        {
            DataSet ds = new DataSet();
            string error = "", sql = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string fromdatabase = "loreal";
            sql = "delete " + DBName + ".dbo.ARINVOICETRANSITION where PersonKey = '" + RSUkey + "'";
            dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            sql = " insert into " + DBName + ".dbo.ARINVOICETRANSITION(Chanel, InvNum, PersonKey, InvAmt, InvCustomer, TransDate, PersonName, PayAmt, PayNumber, PayDisc, PickNum)" +
                " SELECT Chanel, InvNum, PersonKey, InvAmt, InvCustomer, TransDate, PersonName, PayAmt, PayNumber, PayDisc, PickNum FROM " + fromdatabase + ".dbo.ARINVOICETRANSITION WHERE     (TransDate = '" + DateTime.Now.ToString("dd/MM/yyyy") + "') AND (PersonKey = '" + RSUkey + "')";
            dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            sql = " insert into " + DBName + ".dbo.ARINVOICETRANSITION(Chanel, InvNum, PersonKey, InvAmt, InvCustomer, TransDate, PersonName, PayAmt, PayNumber, PayDisc, PickNum)" +
                " SELECT     chanel, tranno, RSUKey, doctot, customer_key, invdate, RSU, PayAmt, PayNumber, PayDisc, PickNum FROM  " + fromdatabase + ".dbo.ARHDR WHERE     (RSUKey = '" + RSUkey + "') AND (invdate = '" + DateTime.Now.ToString("dd/MM/yyyy") + "')";
            dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            sql = " insert into " + DBName + ".dbo.ARINVOICETRANSITION(Chanel, InvNum, PersonKey, InvAmt, InvCustomer, TransDate, PersonName, PayAmt, PayNumber, PayDisc, PickNum)" +
                " SELECT     chanel, tranno, RSUKey, doctot, customer_key, invdate, RSU, PayAmt, PayNumber, PayDisc, PickNum FROM  " + fromdatabase + ".dbo.ARHDRH WHERE     (RSUKey = '" + RSUkey + "') AND (invdate = '" + DateTime.Now.ToString("dd/MM/yyyy") + "')";
            dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            sql = "SELECT     InvNum,InvAmt, InvCustomer,PickNum FROM  " + DBName + ".dbo.ARINVOICETRANSITION WHERE     (TransDate = '" + DateTime.Now.ToString("dd/MM/yyyy") + "') AND (PersonKey = '" + RSUkey + "')";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string UploadShopvisit(string CustomerKey, string Lat, string Long, string DateVisit, string usercode, string BeginTime, string EndTime, string sitecode, string cMode)
        {
            string date = DateTime.Now.ToString("dd/MM/yyyy");
            string MMdd = DateTime.Now.ToString("MM/dd/yyyy");
            string[] spBegin = BeginTime.Split(' ');
            string[] spEnd = EndTime.Split(' ');
            if (int.Parse(spBegin[1].Split(':')[0]) >= 0 && int.Parse(spBegin[1].Split(':')[0]) <= 6)
            {
                spBegin[1] = (int.Parse(spBegin[1].Split(':')[0]) + 12).ToString() + ":" + spBegin[1].Split(':')[1] + ":" + spBegin[1].Split(':')[2];
            }
            if (int.Parse(spEnd[1].Split(':')[0]) >= 0 && int.Parse(spEnd[1].Split(':')[0]) <= 6)
            {
                spEnd[1] = (int.Parse(spEnd[1].Split(':')[0]) + 12).ToString() + ":" + spEnd[1].Split(':')[1] + ":" + spEnd[1].Split(':')[2];
            }
            string sql = "";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            sql = "Update " + DBName + ".dbo.ARCUST set Lat=" + Lat + ",Long =" + Long + " where customer_key='" + CustomerKey + "'  and (Lat =0 or lat is null) and ApprovalGPS<>1";
            bool fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
            sql = "select CustomerKey  from " + DBName + ".dbo.shopvisit where  CustomerKey='" + CustomerKey + "' and DateVisit='" + DateVisit + "'";
            string cust = dal.GetString(sql);
            if (cust == "")
                sql = "insert into " + DBName + ".dbo.ShopVisit(CustomerKey, Lat, Long, DateVisit, isChangeAddress,usercode,BeginTime,EndTime,cMode) " +
                    " values('" + CustomerKey + "'," + Lat + "," + Long + ",'" + DateVisit + "',0,'" + usercode + "',Convert(datetime,'" + MMdd + " " + spBegin[1] + "'),Convert(datetime,'" + MMdd + " " + spEnd[1] + "'),'" + cMode + "')";
            else
                sql = "update " + DBName + ".dbo.shopvisit set EndTime=Convert(datetime,'" + MMdd + " " + spEnd[1] + "') where CustomerKey='" + CustomerKey + "' and DateVisit='" + DateVisit + "'";

            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
            if (fl)
                return "1";
            else
                return "0";
        }
        [WebMethod]
        public string UploadSaleReport(string SiteCode, string PhoneNumber, string ReportDate, string ReportTopicKey, string ReportValue, string CustomerKey, string ReportPicture, string type)
        {
            string date = DateTime.Now.ToString("dd/MM/yyyy");
            DAL dal = new DAL();
            string DBName = GetDatabaseName(SiteCode, dal);
            string SvName = GetServerName(SiteCode, dal);
            bool fl = false;
            string error = "", sql = "";
            if (type == "0")
            {
                sql = "insert into " + DBName + ".dbo.DailySalesReports(SiteCode,PhoneNumber,ReportDate,ReportTopicKey,CustomerKey) values('" + SiteCode + "','" + PhoneNumber + "','" + ReportDate + "','" + ReportTopicKey + "','" + CustomerKey + "')";
                fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
            }
            else
            {
                if (type == "1")
                {
                    sql = "insert into " + DBName + ".dbo.DailySalesReports(SiteCode,PhoneNumber,ReportDate,ReportTopicKey,ReportValue,CustomerKey) values('" + SiteCode + "','" + PhoneNumber + "','" + ReportDate + "','" + ReportTopicKey + "'," + ReportValue + ",'" + CustomerKey + "')";
                    fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                }
                else
                {
                    if (type == "2")
                    {
                        byte[] imageBytes = Convert.FromBase64String(ReportPicture);
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            cmd.Parameters.Add(new SqlParameter("@SiteCode", SiteCode));
                            cmd.Parameters.Add(new SqlParameter("@PhoneNumber", PhoneNumber));
                            cmd.Parameters.Add(new SqlParameter("@ReportDate", ReportDate));
                            cmd.Parameters.Add(new SqlParameter("@ReportTopicKey", ReportTopicKey));
                            cmd.Parameters.Add(new SqlParameter("@CustomerKey", CustomerKey));
                            cmd.Parameters.Add(new SqlParameter("@ReportPicture", imageBytes));
                            cmd.CommandText = "insert into DailySalesReports(SiteCode,PhoneNumber,ReportDate,ReportTopicKey,CustomerKey,ReportPicture)" +
                                " values(@SiteCode,@PhoneNumber,@ReportDate,@ReportTopicKey,@CustomerKey, @ReportPicture)";
                            string sMyConnectionString = @"Data Source=" + SvName + ";User Id=admin;Password=baccamay;Initial Catalog = " + DBName;

                            using (SqlConnection con = new SqlConnection(sMyConnectionString))
                            {
                                cmd.Connection = con;
                                con.Open();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }

            if (fl)
                return "1";
            else
                return "0";
        }
        [WebMethod]
        public string GetuserType(string user, string IME, string sitecode, string Salespkey)
        {
            DAL dal = new DAL();
            string usertype = "0";
            string error = "", sql = "";
            sql = "select IME from MobileUsersSpicial where IME='" + IME + "'";
            string IMESpical = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            if (IMESpical != "")
            {
                usertype = "1";
            }
            else
            {
                sql = "SELECT IME FROM MobileUsers where (UserCode = '" + Salespkey + "') AND (SiteCode = '" + sitecode + "')";
                string IMEs = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                if (IMEs == "")
                {
                    sql = "SELECT UserCode+'_'+sitecode FROM MobileUsers WHERE IME='" + IME + "'";
                    string salesp = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                    if (salesp == "")
                    {
                        sql = "Update MobileUsers set IME = '" + IME + "',phonenumber='" + user + "',active = 1, ModifineDate=Getdate() where (UserCode = '" + Salespkey + "') AND (SiteCode = '" + sitecode + "')";
                        bool kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                        if (kq)
                            usertype = "1";
                        else
                            usertype = "2";
                    }
                    else
                    {
                        usertype = salesp;
                    }

                }

                else
                {
                    if (IME == IMEs)
                    {
                        sql = "Update MobileUsers set IME = '" + IME + "',phonenumber='" + user + "',active = 1, ModifineDate=Getdate() where (UserCode = '" + Salespkey + "') AND (SiteCode = '" + sitecode + "')";
                        bool kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                        if (kq)
                            usertype = "1";
                        else
                            usertype = "2";
                    }
                    else
                        usertype = IMEs;
                }
            }
            return usertype;
        }
        [WebMethod]
        public DataSet GetMenu()
        {
            DataSet ds = new DataSet();
            string sql = "SELECT     ID, Type, Description, imge, loction, usertype, inmagevalues FROM MENU where isedit=1";
            string error = "";
            DAL dal = new DAL();
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetGroupMenu()
        {
            DataSet ds = new DataSet();
            string sql = "SELECT     Groupid, Menuid FROM  GroupMenu";
            string error = "";
            DAL dal = new DAL();
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetINMAST(string sitecode, string Chanel)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     inm.itemkey, inm.description, inm.purchconvfctr, inm.productkey, inm.commodkey, inm.inclasskey, CASE WHEN intypekey IS NULL THEN '001' ELSE intypekey END AS intypekey, inm.price, inm.saletaxkey, inm.FindField, CASE WHEN SaleSerial IS NULL " +
                      " THEN 0 ELSE SaleSerial END AS SaleSerial, CASE WHEN MoveSerial IS NULL THEN 0 ELSE MoveSerial END AS MoveSerial, CASE WHEN inl.qtyonhand > 0 THEN inl.qtyonhand ELSE 0 END AS qtyonhand " +
                      " FROM   " + DBName + ".dbo.INMAST AS inm LEFT OUTER JOIN  " + DBName + ".dbo.INLOC AS inl ON inm.itemkey = inl.itemkey";
            //string sql = "SELECT     itemkey, description, purchconvfctr, productkey, commodkey, inclasskey,case when intypekey is null then '001' else intypekey end as intypekey, price, saletaxkey,FindField,CASE WHEN SaleSerial IS NULL THEN 0 ELSE SaleSerial END AS SaleSerial," +
            //     " CASE WHEN MoveSerial IS NULL THEN 0 ELSE MoveSerial END AS MoveSerial FROM " + DBName + ".dbo.INMAST where abckey='A' and itemkey in(select itemkey from " + DBName + ".dbo.INMASTCHANEL)";
            //sql = "SELECT  DISTINCT   inm.itemkey, inm.description, inm.purchconvfctr, inm.productkey, inm.commodkey, inm.inclasskey, CASE WHEN intypekey IS NULL THEN '001' ELSE intypekey END AS intypekey, inm.price, inm.saletaxkey, inm.FindField, CASE WHEN SaleSerial IS NULL " +
            //" THEN 0 ELSE SaleSerial END AS SaleSerial, CASE WHEN MoveSerial IS NULL THEN 0 ELSE MoveSerial END AS MoveSerial,1 AS qtyonhand " +
            //" FROM   " + DBName + ".dbo.INMAST AS inm LEFT OUTER JOIN  " + DBName + ".dbo.INLOC AS inl ON inm.itemkey = inl.itemkey";

            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetINCOMMOD(string sitecode)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     commodkey, description FROM  INCOMMOD";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetINPRODCT(string sitecode)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     productkey, description FROM INPRODCT";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetINCLASS(string sitecode)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     inclasskey, description FROM  INCLASS";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetINTYPE(string sitecode)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     intypekey, Description FROM  INTYPE";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        
        [WebMethod]
        public string UploadINTXD(string INTXD, string approv, string sitecode)
        {
            //cau truc sitecode.manv.sohd.ngay.masp.so...
            string error = "", sql = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            bool fl = false;

            string[] INTXDDetail = INTXD.Split('.');
            try
            {
                string date = DateTime.Now.ToString("dd/MM/yyyy");
                sql = "SELECT     SaleLocation FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + INTXDDetail[1].ToString() + "')";
                string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                if (Prloc == "")
                    return "0";
                else
                {
                    sql = "SELECT     ApproveINTXDbySS FROM  " + DBName + ".dbo.ARSALESPTYPE";
                    string approve = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                    if (approve == "1")
                    {
                        approve = "0";
                    }
                    else
                        approve = "1";

                    sql = "delete " + DBName + ".dbo.INTXD where docno='M_" + INTXDDetail[1].ToString() + "_" + INTXDDetail[2] + "_" + DateTime.Now.ToString("ddMMyyyy") + "'";
                    fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                    if (fl)
                    {
                        for (int i = 4; i < INTXDDetail.Length; i = i + 2)
                        {
                            sql = "insert into " + DBName + ".dbo.INTXD( itemkey, location, trntyp, docno, docdate, tolocation, stockqty, Recuser, chanel, Approve)" +
                            " values('" + INTXDDetail[i] + "','WH001','M','M_" + INTXDDetail[1].ToString() + "_" + INTXDDetail[2] + "_" + DateTime.Now.ToString("ddMMyyyy") + "','" + date + "','" + Prloc + "'," + INTXDDetail[i + 1] + ",'" + INTXDDetail[1] + "',1," + approve + ")";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                        }
                        return "1";
                    }
                    else
                        return "0";
                }
            }
            catch (Exception ex)
            {
                return "0";
            }
        }
        [WebMethod]
        public string UploadOrder(string Order, string sitecode)
        {
            //cau truc phanbietsalorder.sitecode.manv.makh.sohd.ngay.masp.so...
            string error = "", sql = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            bool fl = false;

            string[] OrderDetail = Order.Split('.');
            try
            {
                if (OrderDetail[3] == "0" || OrderDetail[3] == "")
                {
                    return "0";
                }
                else
                {
                    if (OrderDetail[3].Substring(0, 1) != "_")
                    {
                        string date = DateTime.Now.ToString("dd/MM/yyyy");
                        string vanloc = OrderDetail[0];
                        sql = "SELECT    case when  vanloc is null then 1 else vanloc end vanloc  FROM  " + DBName + ".dbo.ARSALESPTYPE";
                        string van = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                        string location = "WH001";
                        if (van != "2")
                            vanloc = van;
                        //string date = DateTime.Now.ToString("dd/MM/yyyy");
                        sql = "SELECT     SaleLocation FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + OrderDetail[2].ToString() + "')";
                        string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                        if (Prloc != "" && vanloc == "1")
                            location = Prloc;
                        sql = "SELECT     tranno FROM  " + DBName + ".dbo.ARHDR where applytodocno='" + OrderDetail[4] + "' and Salespkey='" + OrderDetail[2].ToString() + "' and invdate='" + date + "'";
                        string tranno = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                        if (tranno == "")
                        {
                            sql = "SELECT     NextArinvoice FROM  " + DBName + ".dbo.ARSALESPTYPE";
                            tranno = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                            sql = "update " + DBName + ".dbo.ARSALESPTYPE set NextArinvoice=NextArinvoice+1";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                        }
                        if (tranno == "")
                            return "0";
                        else
                        {
                            sql = "delete " + DBName + ".dbo.ARLIN where tranno in(select tranno from " + DBName + ".dbo.ARHDR where customer_key='" + OrderDetail[3] + "' and invdate='" + date + "')";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            sql = "delete " + DBName + ".dbo.ARHDR where customer_key='" + OrderDetail[3] + "' and invdate='" + date + "'";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);

                            sql = "insert into " + DBName + ".dbo.ARHDR(customer_key, tranno, applytodocno, statusflg, invdate, agedate, location, Salespkey, trntyp, doctot, printed, so, CR_flag, Recdate, Rectime, Recuser, " +
                              " Taxinvtot, IC_flag, chanel, NoneItem, VanLoc, TeamID) values('" + OrderDetail[3] + "','" + tranno + "','" + OrderDetail[4] + "',0,'" + date + "','" + date + "','" + location + "','" + OrderDetail[2] + "','I',0,0,0,0,getdate(),getdate(),'" + OrderDetail[2] + "'," +
                              "0,0,1,0," + vanloc + ",1)";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            for (int i = 6; i < OrderDetail.Length; i = i + 2)
                            {
                                sql = "insert into " + DBName + ".dbo.ARLIN( tranno, itemkey, location, shipqty, docdate, lineitemtyp, convkey, trntyp, Recdate, Rectime, Recuser)" +
                                " values('" + tranno + "','" + OrderDetail[i] + "','" + location + "','" + OrderDetail[i + 1] + "','" + date + "','0',1,'I',getdate(),getdate(),'" + OrderDetail[2] + "')";
                                fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            }
                            sql = "update  " + DBName + ".dbo.ARLIN set unitprice=inmast.price from " + DBName + ".dbo.inmast where arlin.itemkey=inmast.itemkey and tranno='" + tranno + "'";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            sql = "update " + DBName + ".dbo.ARHDR set doctot =dtot from (SELECT     tranno, SUM(unitprice * shipqty) AS dtot FROM  " + DBName + ".dbo.ARLIN" +
                                " GROUP BY tranno HAVING      (tranno = '" + tranno + "')) as arlin where ARHDR.tranno=arlin.tranno and ARHDR.tranno='" + tranno + "'";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            sql = "update " + DBName + ".dbo.ShopVisit set docamt=doctot from " + DBName + ".dbo.arhdr where customer_key=CustomerKey and invdate=DateVisit and Salespkey='" + OrderDetail[2] + "' and invdate='" + date + "'";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            return "1";
                        }
                    }
                }
                return "0";
            }
            catch (Exception ex)
            {
                return "0";
            }
        }
        [WebMethod]
        public string UploadAllOrder(string Order, string sitecode, string salespkey, string invdate)
        {
            //cau truc dauhieuphanbiethoadonpresalehaydirectsale.sohoadon.makh1.msp1.sl1.msp2.sl2..@dauhieuphanbiethoadonpresalehaydirectsale.sohoadon.makh2.msp1.sl1.msp2.sl2..
            string error = "", sql = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            bool fl = false;
            string Result = "0";
            string date = DateTime.Now.ToString("dd/MM/yyyy");
            string[] O = Order.Split('@');
            for (int y = 0; y < O.Length; y++)
            {
                string[] OrderDetail = O[y].Split('.');
                try
                {
                    if (OrderDetail[2].Substring(0, 1) != "_")
                    {
                        sql = "Select customer_key from arcust where customer_key='" + OrderDetail[2] + "'";
                        string cuskey = dal.MyExecuteReaders(ref error, sql, CommandType.Text);

                        string vanloc = OrderDetail[0];
                        sql = "SELECT    case when  vanloc is null then 1 else vanloc end vanloc  FROM  " + DBName + ".dbo.ARSALESPTYPE";
                        string van = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                        string location = "WH001";
                        if (van != "2")
                            vanloc = van;
                        //string date = DateTime.Now.ToString("dd/MM/yyyy");
                        sql = "SELECT     SaleLocation FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + salespkey + "')";
                        string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                        if (Prloc != "" && vanloc == "1")
                            location = Prloc;
                        sql = "SELECT     tranno FROM  " + DBName + ".dbo.ARHDR where applytodocno='" + OrderDetail[1] + "' and Salespkey='" + salespkey + "' and invdate='" + date + "'";
                        string tranno = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                        if (tranno == "")
                        {
                            sql = "SELECT     NextArinvoice FROM  " + DBName + ".dbo.ARSALESPTYPE";
                            tranno = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                            sql = "update " + DBName + ".dbo.ARSALESPTYPE set NextArinvoice=NextArinvoice+1";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                        }
                        if (tranno == "")
                            continue;
                        else
                        {
                            sql = "delete " + DBName + ".dbo.ARLIN where tranno in(select tranno from " + DBName + ".dbo.ARHDR where customer_key='" + OrderDetail[2] + "' and invdate='" + date + "')";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            sql = "delete " + DBName + ".dbo.ARHDR where customer_key='" + OrderDetail[2] + "' and invdate='" + date + "'";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            if (cuskey != "")
                            {
                                sql = "insert into " + DBName + ".dbo.ARHDR(customer_key, tranno, applytodocno, statusflg, invdate, agedate, location, Salespkey, trntyp, doctot, printed, so, CR_flag, Recdate, Rectime, Recuser, " +
                                  " Taxinvtot, IC_flag, chanel, NoneItem, VanLoc, TeamID) values('" + OrderDetail[2] + "','" + tranno + "','" + OrderDetail[1] + "',0,'" + date + "','" + date + "','" + location + "','" + salespkey + "','I',0,0,0,0,getdate(),getdate(),'" + salespkey + "'," +
                                  "0,0,1,0," + vanloc + ",1)";
                                fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                                for (int i = 3; i < OrderDetail.Length; i = i + 2)
                                {
                                    sql = "insert into " + DBName + ".dbo.ARLIN( tranno, itemkey, location, shipqty, docdate, lineitemtyp, convkey, trntyp, Recdate, Rectime, Recuser)" +
                                    " values('" + tranno + "','" + OrderDetail[i] + "','" + location + "','" + OrderDetail[i + 1] + "','" + date + "','0',1,'I',getdate(),getdate(),'" + salespkey + "')";
                                    fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                                }
                                sql = "update  " + DBName + ".dbo.ARLIN set unitprice=inmast.price from " + DBName + ".dbo.inmast where arlin.itemkey=inmast.itemkey and tranno='" + tranno + "'";
                                fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                                sql = "update " + DBName + ".dbo.ARHDR set doctot =dtot from (SELECT     tranno, SUM(unitprice * shipqty) AS dtot FROM  " + DBName + ".dbo.ARLIN" +
                                    " GROUP BY tranno HAVING      (tranno = '" + tranno + "')) as arlin where ARHDR.tranno=arlin.tranno and ARHDR.tranno='" + tranno + "'";
                                fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                                sql = "update " + DBName + ".dbo.ShopVisit set docamt=doctot from " + DBName + ".dbo.arhdr where customer_key=CustomerKey and invdate=DateVisit and Salespkey='" + salespkey + "' and invdate='" + date + "'";
                                fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);

                            }
                        }
                    }
                    else
                        continue;
                }
                catch
                {
                    continue;
                }
            }
            sql = "SELECT     COUNT(tranno) AS tranno FROM  " + DBName + ".dbo.ARHDR WHERE     (Salespkey = '" + salespkey + "') AND (invdate = '" + date + "')";
            Result = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            return Result;
        }
        [WebMethod]
        public DataSet GetInLoc(string salespkey, string sitecode)
        {
            //string server = "192.168.1.81";
            //string database = "MobileCenter";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string sql = "SELECT     SaleLocation FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + salespkey + "')";
            string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();

            sql = "SELECT     itemkey, qtyonhand FROM " + DBName + ".dbo.INLOC WHERE (location = '" + Prloc + "')";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetInLocSerial(string salespkey, string sitecode)
        {
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string sql = "SELECT     SaleLocation FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + salespkey + "')";
            string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            sql = "SELECT    location, itemkey, Qty, Status, serial, serial1, indate FROM " + DBName + ".dbo.INLOCSERIAL WHERE Qty=1 and (tranno='' or tranno is null) and   (location = '" + Prloc + "')";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }

        [WebMethod]
        public DataSet GetSetting(string sitecode, string usercode, string usertype)
        {
            //string server = "192.168.1.81";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string AppStatus = "1";
            string error = "", sql = "", temp = "";
            if (usertype.ToUpper() == "DSR")
            {
                sql = "SELECT     AppStatus FROM " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + usercode + "')";
            }
            else
                sql = "SELECT Area as  AppStatus FROM ARSS WHERE     (SScode = '" + usercode + "')";
            temp = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            if (temp != "")
                AppStatus = temp;

            sql = "SELECT    '" + AppStatus + "' as AppStatus, ShowPrice, PriceType, PaycashAfterPromotion, AddinAfterPromotion, NextArinvoice, Checkinloc,Sitename,ManagerSerial,OrderwhenVisit," + DateTime.Today.ToString("yyyyMMdd") + " as ClosedWork FROM " + DBName + ".dbo.ARSALESPTYPE";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetProvince(string Sitecode)
        {
            //string server = "192.168.1.81";
            //string database = "MobileCenter";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(Sitecode, dal);
            string sql = "";
            //string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            sql = "SELECT     ProvinceCode, ProvinceName FROM  PROVINCE";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetTown(string sitecode)
        {
            //string server = "192.168.1.81";
            //string database = "MobileCenter";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);

            string sql = "SELECT     ProvinceCode, TownCode, TownName FROM DistrictTown";
            //string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            //sql = "SELECT     itemkey, qtyonhand FROM INLOC WHERE (location = '" + Prloc + "')";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetSalesp(string ID, string sitecode, string type)
        {
            DataSet ds = new DataSet();
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string error = "";
            string sql = "";
            if (type == "dsr")
                sql = "SELECT     salesp_key, salesp_name, SSCode, Phone FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + ID + "')";
            else

                sql = "SELECT     salesp_key, salesp_name, SSCode, Phone FROM " + DBName + ".dbo.ARSALESP WHERE     (SSCode = '" + ID + "')";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }

        [WebMethod]
        public DataSet GetINTXD(string ID, string sitecode, string type)
        {
            DataSet ds = new DataSet();
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string error = "";
            string sql = "";
            sql = "SELECT     itemkey, location, docno, docdate, tolocation, stockqty,Recuser FROM  " + DBName + ".dbo.INTXD WHERE     (Approve = 0)";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string DowloadVersion(string currenversion, string IME)
        {
            string version = "-1";
            try
            {

                string error = "", sql = "";
                DAL dal = new DAL();
                sql = "update MobileUsers set Version='" + currenversion + "' where (IME = '" + IME + "')";
                dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
                sql = "SELECT     UserCode FROM MobileUsers WHERE     (active = 1) AND (IsDowloadVersion = 1) AND (IME = '" + IME + "')";
                string UserCode = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
                //if (UserCode != "" || UserCode == "")
                {
                    sql = "SELECT VersionCode FROM NewVersion";
                    DataSet ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
                    if (int.Parse(ds.Tables[0].Rows[0]["VersionCode"].ToString()) > int.Parse(currenversion))
                    {
                        sql = "SELECT VersionCode,Data FROM NewVersion";
                        ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
                        byte[] encodedDataAsBytes = (byte[])ds.Tables[0].Rows[0]["DATA"];
                        version = System.Convert.ToBase64String(encodedDataAsBytes);
                    }
                    else
                        version = "-1";
                }
                // else
                // version = "-1";
            }
            catch
            {
                version = "-1";
            }
            return version;
        }
        [WebMethod]
        public DataSet GetPROMOTIONDEFINE(string sitecode, string key)
        {
            string date = DateTime.Now.ToString("yyyy-MM-dd");
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string sql = "", error = "";
            DataSet ds = new DataSet();
            if (key == "1")
                sql = "Select Program_ID, Program_Des, [To], [From], Program_type, Priority, ObjType, OneTime, FixPrize, Focus, AutoControlBudget FROM  " + DBName + ".dbo.ARPRODEF WHERE     ([From] <= '" + date + "')";
            if (key == "2")
                sql = "SELECT     Program_ID, itemkey, Operator, BuyQuantity FROM " + DBName + ".dbo.ProBuyDef where program_id in(Select Program_ID FROM  " + DBName + ".dbo.ARPRODEF WHERE     ([From] <= '" + date + "'))";
            if (key == "3")
                sql = "Program_ID, BuyLevel, CalByQuantity, PayItemkey, PayPrice, PayQuantity, PayType, IsDirectSale FROM " + DBName + ".dbo.ProPayDef where program_id in(Select Program_ID FROM  " + DBName + ".dbo.ARPRODEF WHERE     ([From] <= '" + date + "'))";
            if (key == "4")
                sql = "SELECT     Program_ID, itemkey, Price, BuyLevel, PayQuantity, PayOption FROM  " + DBName + ".dbo.ARPROPAYITEM where program_id in(Select Program_ID FROM  " + DBName + ".dbo.ARPRODEF WHERE     ([From] <= '" + date + "'))";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }

        [WebMethod]
        public string InsertChangeAddressByDSR(string customerkey, string Sitecode, string Customername, string shopowner, string address,
            string street, string ward, string towncode, string provincecode, string DeliveryAddress, string phone,
            string T, string TradeSegment, string SubTradeSegment, string Otype, string VLevel, string StatusType, string salekey, string Lat, string Long)
        {
            string error = "";
            string cuskey = customerkey;
            DAL dal = new DAL();
            ConvertFont convert = new ConvertFont();
            convert.Convert(ref Customername, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref address, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref shopowner, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref shopowner, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref ward, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref street, FontIndex.iUNI, FontIndex.iVNI);
            convert.Convert(ref DeliveryAddress, FontIndex.iUNI, FontIndex.iVNI);
            string findfield = convertToUnSign2(Customername + DeliveryAddress);
            string DBName = GetDatabaseName(Sitecode, dal);
            string sql = "";

            sql = "Insert into " + DBName + ".dbo.ChangeAddress(customerkey, customername, address,customernameUNI, addressUNI, Tel, [credit limit], terms, salepersonkey, ProvinceCode, TownCode, MCP2, MCP3, MCP4, MCP5, MCP6, MCP7, " +
                       "   Active, Vol, TradeSegment, Freq, Otype, Repeat, Manual, AvgOff, TopOff, BizControl, Chanel, VanDSR, location_Key, RSUCust, RSUKey, " +
                       "   Arsalepersonkey, DeliveryAddress,DeliveryAddressUNI, TeamID, isShow, Lat, Long, Ward, Street, RecTime, RecUser, ShopOwner, sitecode,SubTradeSegment,VLevel,StatusType,FindField)" +
                       " values('" + cuskey + "','" + Customername + "','" + address + "',N'" + Customername + "',N'" + address + "','" + phone + "',500000,7,'" + salekey + "','" + provincecode + "','" + towncode + "',0,0,0,0,0,0" +
                       ",0,0,'" + TradeSegment + "',4,'',0,0,0,0,0,1,0,'WH001',0,0,'" + salekey + "','" + DeliveryAddress + "',N'" + DeliveryAddress + "',1,0," + Lat + "," + Long + ",'" + ward + "','" + street + "',getdate(),'" + salekey + "','" + shopowner + "','" + Sitecode + "','" + SubTradeSegment + "','" + VLevel + "','" + StatusType + "',N'" + findfield + "' )";
            bool kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);

            if (T == "2")
            {
                sql = "update " + DBName + ".dbo.ChangeAddress set MCP2=1 where customerkey='" + cuskey + "'";
            }
            if (T == "3")
            {
                sql = "update " + DBName + ".dbo.ChangeAddress set MCP3=1 where customerkey='" + cuskey + "'";
            }
            if (T == "4")
            {
                sql = "update " + DBName + ".dbo.ChangeAddress set MCP4=1 where customerkey='" + cuskey + "'";
            }
            if (T == "5")
            {
                sql = "update " + DBName + ".dbo.ChangeAddress set MCP5=1 where customerkey='" + cuskey + "'";
            }
            if (T == "6")
            {
                sql = "update " + DBName + ".dbo.ChangeAddress set MCP6=1 where customerkey='" + cuskey + "'";
            }
            if (T == "7")
            {
                sql = "update " + DBName + ".dbo.ChangeAddress set MCP7=1 where customerkey='" + cuskey + "'";
            }
            if (kq)
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref error);
            if (kq)
                return cuskey;
            else
                return "0";
        }

        [WebMethod]
        public DataSet SKUBizByCustomer(string salespkey, string sitecode)
        {
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string sql = "SELECT     SaleLocation FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + salespkey + "')";
            string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            sql = "SELECT     CustomerKey, SKUCode, FirstBuy, LastBuy, AvgQty,case when AvgAmt is null then 0 else AvgAmt end as AvgAmt,case when BizControlByCustomer is null then 0 else BizControlByCustomer end as BizControlByCustomer ,case when BizControlTotal is null then 0 else BizControlTotal end as BizControlTotal," +
                " case when BizTrend is null then 0 else BizTrend end as BizTrend,case when TargetByQty is null then 0 else TargetByQty end as TargetByQty ,case when TargetByAmt is null then 0 else TargetByAmt end as TargetByAmt, " +
                  " case when MonthToDateQty is null then 0 else MonthToDateQty end as MonthToDateQty , case when MothToDateAmt is null then 0 else MothToDateAmt end as MothToDateAmt , chanel, TeamID FROM  " + DBName + ".dbo.SKUBizByCustomer where CustomerKey in(SELECT     customerkey FROM   " + DBName + ".dbo.TCARCUST WHERE     (salepersonkey = '" + salespkey + "'))";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetDistributor(string Sitecode)
        {
            //string server = "192.168.1.81";
            //string database = "MobileCenter";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(Sitecode, dal);
            string sql = "";
            //string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            sql = "SELECT     Sitecode, SiteName, DBName,Address FROM  Distributor";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetArSaleSP(string Sitecode)
        {
            //string server = "192.168.1.81";
            //string database = "MobileCenter";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(Sitecode, dal);
            string sql = "";
            //string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            sql = "SELECT     SiteCode, Salesp_Key, SaleSp_name,SSCode FROM  ARSALESP WHERE     (active = 1)";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetSS(string Sitecode)
        {
            //string server = "192.168.1.81";
            //string database = "MobileCenter";
            string error = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(Sitecode, dal);
            string sql = "";
            //string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            sql = "SELECT     SScode, SSName FROM  ARSS WHERE     (active = 1)";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetDistributorManager(string SSCode)
        {
            //string server = "192.168.1.81";
            //string database = "MobileCenter";
            string error = "";
            DAL dal = new DAL();
            string sql = "";
            //string Prloc = dal.MyExecuteReaders(ref error, sql, CommandType.Text);
            DataSet ds = new DataSet();
            sql = "SELECT     DistributorManager.SSCode, DistributorManager.Sitecode, Distributor.SiteName,Distributor.Chanel FROM   DistributorManager INNER JOIN Distributor ON DistributorManager.Sitecode = Distributor.Sitecode" +
                   " WHERE     (DistributorManager.SSCode = '" + SSCode + "')";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetCustType(string Sitecode, string Type)
        {
            //string date = DateTime.Now.ToString("yyyy-MM-dd");
            DAL dal = new DAL();
            string sql = "", error = "";
            //string DBName = GetDatabaseName(Sitecode, dal);
            DataSet ds = new DataSet();
            if (Type == "Trad")
                sql = "SELECT     Class_Key as ID, Class_Descr as Name FROM ARCLASS";
            if (Type == "SubTrad")
                sql = "SELECT  SubClass_key as ID, SubClass_Desc as Name FROM ARSUBCLASS";
            if (Type == "Otype")
                sql = "SELECT     StrategicKey as ID, StrategicName as Name FROM dbo.STRATEGICTYPE";
            if (Type == "Vlevel")
                sql = "SELECT     VLevel as ID, Description as Name FROM ARVOLUMELEVEL";
            if (Type == "Status")
                sql = "SELECT     StatusType as ID, Description as Name FROM  AROUTLETSTATUS";

            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetShopVist(string sitecode, string day)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT   DISTINCT   CustomerKey, Lat, Long, DateVisit, usercode, CASE WHEN DocAmt IS NULL THEN 0 ELSE DocAmt END AS DocAmt FROM  " + DBName + ".dbo.ShopVisit where CONVERT (varchar(10), BeginTime, 102)>= '" + DateTime.Today.AddDays(-double.Parse(day)).ToString("yyyy.MM.dd") + "'";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string ChangeRoute(string sitecode, string values)
        {
            //makc;active;t2;t3;t4;t5;t6;t7;freq;trad;subtrad;otype;vlevel;status;manhanvien
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            try
            {
                string[] vl = values.Split(';');
                string sql = "Update " + DBName + ".dbo.TCARCUST set Active=" + vl[1] + ",MCP2=" + vl[2] + ",MCP3=" + vl[3] + ",MCP4=" + vl[4] + ",MCP5=" + vl[5] + ",MCP6=" + vl[6] + ",MCP7=" + vl[7] + "," +
                    "Freq=" + vl[8] + ",TradeSegment='" + vl[9] + "',SubTradeSegment='" + vl[10] + "',Otype='" + vl[11] + "',VLevel='" + vl[12] + "',StatusType='" + vl[13] + "',salepersonkey='" + vl[14] + "'," +
                    "MCPORD2=" + vl[15] + ",MCPORD3=" + vl[16] + ",MCPORD4=" + vl[17] + ",MCPORD5=" + vl[18] + ",MCPORD6=" + vl[19] + ",MCPORD7=" + vl[20] + ",isShow=1 where customerkey='" + vl[0] + "'";
                string error = "";
                bool kq = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                if (kq)
                    return "1";
                else
                    return "0";
            }
            catch
            {
                return "0";
            };
        }
        [WebMethod]
        public DataSet CreditControl(string login_name, int login_chanel, int TeamID, string sitecode)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            bool IsInsert = false;
            DataTable temp = new DataTable();
            // ERROR: Not supported in C#: OnErrorStatement
            DAL dal = new DAL();
            string err = "";
            string DBName = GetDatabaseName(sitecode, dal);
            string SVName = GetServerName(sitecode, dal);
            bool kq = dal.MyExecuteNonQuerys("Drop table " + login_name + "AROVERINVOICE", SVName, DBName);

            kq = dal.MyExecuteNonQuerys("CREATE TABLE [dbo].[" + login_name + "AROVERINVOICE] ([Customer] [varchar] (50)   NULL,[InvNum] [varchar] (50)   NULL,[InvDate] [varchar] (50)   NULL,[InvAmt] Float NULL ,[Reason] [varchar] (350)   NULL ) ON [PRIMARY]", SVName, DBName);

            kq = dal.MyExecuteNonQuerys("Update ARHDR  Set CR_flag=1 WHERE CRApproval =0 And statusflg=1  And trntyp = 'I' AND so = 1 And ARHDR.chanel=" + login_chanel + " and IsCash=1", SVName, DBName);
            dt = dal.GetTable("SELECT Cr_Hold_Flag,ARHDR.customer_key, ARHDR.customer_key + '_' + ARCUST.customer_name + Case When address Is Null then '' Else ' ' + address End +Case When street Is Null then '' Else ' ' + street End as Customer, ARHDR.tranno, ARHDR.invdate, terms, cr_limit_amt,Cr_Hold_Flag, ARHDR.doctot AS InvAmt, ARHDR.CR_flag,arhdr.chanel,ARCUST.Invstilldue FROM " + DBName + ".dbo.ARCUST INNER JOIN " + DBName + ".dbo.ARHDR ON ARCUST.customer_key = ARHDR.customer_key WHERE CRApproval =0 and statusflg=1  And trntyp = 'I' AND so = 1 And ARHDR.chanel=" + login_chanel + "  And IsCash=0 Order by Salespkey");
            foreach (DataRow data in dt.Rows)
            {
                IsInsert = false;
                //khach hang khong cho no
                if (data.IsNull("Cr_Hold_Flag") || data["Cr_Hold_Flag"].ToString() != "")
                {

                    IsInsert = true;
                    kq = dal.MyExecuteNonQuerys("Insert into " + login_name + "AROVERINVOICE(Customer,InvNum,InvDate,InvAmt,Reason)Values('" + data["Customer"].ToString() + "','" + data["tranno"].ToString() + "','" + data["InvDate"].ToString() + "'," + data["InvAmt"].ToString() + ",'Khaùch haøng khoâng cho nôï,')", SVName, DBName);
                }

                //qua gioi han cong no
                bool CreditLimitBySaleman = false;
                if (CreditLimitBySaleman)
                {
                    if (!dal.GetTable("SELECT dbo.ARSALESP.CreditLimit FROM " + DBName + ".dbo.TCARCUST INNER JOIN " + DBName + ".dbo.ARSALESP ON dbo.TCARCUST.salepersonkey = dbo.ARSALESP.salesp_key  Where customerkey='" + data["customer_key"].ToString() + "'").Rows[0].IsNull("CreditLimit"))
                    {
                        if (float.Parse(dal.GetTable("select " + data["doctot"] + "+ sum(docamt)  as ar from " + DBName + ".dbo.ARTRAN where customer_key In (Select customerkey From " + DBName + ".dbo.TCARCUST Where chanel=" + login_chanel + " And  salepersonkey='" + dal.GetString("Select salepersonkey From " + DBName + ".dbo.TCARCUST Where chanel=" + login_chanel + " Amd customerkey='" + data["customer_key"].ToString() + "'") + "')").Rows[0]["ar"].ToString()) > float.Parse(dal.GetTable("SELECT dbo.ARSALESP.CreditLimit FROM " + DBName + ".dbo.TCARCUST INNER JOIN " + DBName + ".dbo.ARSALESP ON dbo.TCARCUST.salepersonkey = dbo.ARSALESP.salesp_key  Where customerkey='" + data["customer_key"].ToString() + "'").Rows[0]["CreditLimit"].ToString()))
                        {
                            if (IsInsert)
                            {
                                kq = dal.MyExecuteNonQuerys("Update " + login_name + "AROVERINVOICE Set Reason=Reason + 'Qua gioi han cong no nhan vien,' Where InvNum='" + data["tranno"].ToString() + "'", SVName, DBName);
                            }
                            else
                            {
                                IsInsert = true;
                                kq = dal.MyExecuteNonQuerys("Insert into " + login_name + "AROVERINVOICE(Customer,InvNum,InvDate,InvAmt,Reason)Values('" + data["Customer"].ToString() + "','" + data["tranno"].ToString() + "','" + data["InvDate"].ToString() + "'," + data["InvAmt"].ToString() + ",'Qua gioi han cong no nhan vien,')", SVName, DBName);
                            }
                        }
                    }
                }
                else
                {
                    if (!data.IsNull("cr_limit_amt"))
                    {
                        temp = dal.GetTable("select  sum(docamt)  as ar from " + DBName + ".dbo.ARTRAN where customer_key='" + data["customer_key"].ToString() + "'");
                        if (temp.Rows.Count > 0)
                        {
                            if (float.Parse(temp.Rows[0]["ar"].ToString()) + float.Parse(data["InvAmt"].ToString()) > float.Parse(data["cr_limit_amt"].ToString()))
                            {
                                if (IsInsert)
                                {
                                    dal.MyExecuteNonQuerys("Update " + login_name + "AROVERINVOICE Set Reason=Reason + 'Qua gioi han cong no,' Where InvNum='" + data["tranno"].ToString() + "'", SVName, DBName);
                                }
                                else
                                {
                                    IsInsert = true;
                                    dal.MyExecuteNonQuerys("Insert into " + login_name + "AROVERINVOICE(Customer,InvNum,InvDate,InvAmt,Reason)Values('" + data["Customer"].ToString() + "','" + data["tranno"].ToString() + "','" + data["InvDate"].ToString() + "'," + data["InvAmt"].ToString() + ",'Qua gioi han cong no,')", SVName, DBName);
                                }
                            }
                        }
                    }
                }

                //Qua so ngay cho no
                if (!data.IsNull("terms"))
                {
                    temp = dal.GetTable("SELECT     SUBSTRING(docdate, 4, 2)+'/' + LEFT(docdate, 2)+'/'+ RIGHT(docdate, 4)  AS OldDestDate  FROM   " + DBName + ".dbo.ARTRAN where customer_key='" + data["customer_key"].ToString() + "' And trntyp='I' And applytodocno In (SELECT     applytodocno  FROM " + DBName + ".dbo.ARTRAN GROUP BY applytodocno HAVING SUM(docamt) > 0)");
                    if (temp.Rows.Count > 0)
                    {
                        if (!temp.Rows[0].IsNull("OldDestDate"))
                        {
                            if (DateTime.Parse(temp.Rows[0]["OldDestDate"].ToString()).AddDays(double.Parse(data["terms"].ToString())) < DateTime.Now)
                            {
                                if (IsInsert)
                                {
                                    kq = dal.MyExecuteNonQuerys("Update " + login_name + "AROVERINVOICE Set Reason=Reason + 'Thanh toan tre,' Where InvNum='" + data["tranno"].ToString() + "'", SVName, DBName);
                                }
                                else
                                {
                                    IsInsert = true;
                                    dal.MyExecuteNonQuerys("Insert into " + login_name + "AROVERINVOICE(Customer,InvNum,InvDate,InvAmt,Reason)Values('" + data["Customer"].ToString() + "','" + data["tranno"].ToString() + "','" + data["InvDate"].ToString() + "'," + data["InvAmt"].ToString() + ",'Thanh toan tre,')", SVName, DBName);
                                }
                            }
                        }
                    }
                }

                //Qua so hoa don no

                temp = dal.GetTable("SELECT applytodocno  from " + DBName + ".dbo.ARTRAN WHERE (customer_key = '" + data["customer_key"].ToString() + "') Group by applytodocno Having (Sum(docamt) > 0)");
                if (temp.Rows.Count > 0)
                {
                    if (!data.IsNull("Invstilldue"))
                    {
                        if (temp.Rows.Count + 1 >= int.Parse(data["Invstilldue"].ToString()))
                        {
                            if (IsInsert)
                            {
                                kq = dal.MyExecuteNonQuerys("Update " + login_name + "AROVERINVOICE Set Reason=Reason + 'Qua so hoa don no,' Where InvNum='" + data["tranno"].ToString() + "'", SVName, DBName);
                            }
                            else
                            {
                                IsInsert = true;
                                kq = dal.MyExecuteNonQuerys("Insert into " + login_name + "AROVERINVOICE(Customer,InvNum,InvDate,InvAmt,Reason)Values('" + data["Customer"].ToString() + "','" + data["tranno"].ToString() + "','" + data["InvDate"].ToString() + "'," + data["InvAmt"].ToString() + ",'Qua so hoa don cho no,')", SVName, DBName);
                            }
                        }
                    }
                }

                //Doanh so vuot qua doanh so trung binh
                if (!dal.GetTable("Select ARsize From " + DBName + ".dbo.ARCUST Where customer_key='" + data["customer_key"].ToString() + "'").Rows[0].IsNull("ARsize"))
                {
                    if (dal.GetTable("Select AvgOff From " + DBName + ".dbo.TCARCUST Where chanel=" + login_chanel + " And TeamID=" + TeamID + " And customerkey='" + data["Customer_key"] + "'").Rows[0].IsNull("Avgoff"))
                    {
                        if (IsInsert)
                        {
                            kq = dal.MyExecuteNonQuerys("Update " + login_name + "AROVERINVOICE Set Reason=Reason + 'Qua doanh so trung binh(0),' Where InvNum='" + data["tranno"].ToString() + "'", SVName, DBName);
                        }
                        else
                        {
                            IsInsert = true;
                            kq = dal.MyExecuteNonQuerys("Insert into " + login_name + "AROVERINVOICE(Customer,InvNum,InvDate,InvAmt,Reason)Values('" + data["Customer"].ToString() + "','" + data["tranno"].ToString() + "','" + data["InvDate"].ToString() + "'," + data["InvAmt"].ToString() + ",'Qua doanh so trung binh(0),')", SVName, DBName);
                        }
                    }
                    else
                    {
                        if (float.Parse(dal.GetTable("Select ARsize From " + DBName + ".dbo.ARCUST Where customer_key='" + data["customer_key"].ToString() + "'").Rows[0]["ARSize"].ToString()) * float.Parse(dal.GetTable("Select AvgOff From " + DBName + ".dbo.TCARCUST Where chanel=" + login_chanel + " And TeamID=" + TeamID + " And customerkey='" + data["Customer_key"].ToString() + "'").Rows[0]["Avgoff"].ToString()) < float.Parse(data["InvAmt"].ToString()))
                        {
                            if (IsInsert)
                            {
                                kq = dal.MyExecuteNonQuerys("Update " + login_name + "AROVERINVOICE Set Reason=Reason + 'Qua doanh so trung binh(" + float.Parse(dal.GetTable("Select ARsize From " + DBName + ".dbo.ARCUST Where customer_key='" + data["customer_key"].ToString() + "'").Rows[0]["ARSize"].ToString()) * float.Parse(dal.GetTable("Select AvgOff From " + DBName + ".dbo.TCARCUST Where chanel=" + login_chanel + " And TeamID=" + TeamID + " And customerkey='" + data["Customer_key"].ToString() + "'").Rows[0]["Avgoff"].ToString()) + "),'  Where InvNum='" + data["tranno"].ToString() + "'", SVName, DBName);
                            }
                            else
                            {
                                IsInsert = true;
                                kq = dal.MyExecuteNonQuerys("Insert into " + login_name + "AROVERINVOICE(Customer,InvNum,InvDate,InvAmt,Reason)Values('" + data["Customer"].ToString() + "','" + data["tranno"].ToString() + "','" + data["InvDate"].ToString() + "'," + data["InvAmt"].ToString() + ",'Quan doanh so trung binh(" + float.Parse(dal.GetTable("Select ARsize From " + DBName + ".dbo.ARCUST Where customer_key='" + data["customer_key"] + "'").Rows[0]["ARSize"].ToString()) * float.Parse(dal.GetTable("Select AvgOff From " + DBName + ".dbo.TCARCUST Where chanel=" + login_chanel + " And TeamID=" + TeamID + " And customerkey='" + data["Customer_key"].ToString() + "'").Rows[0]["Avgoff"].ToString()) + "),')", SVName, DBName);
                            }
                        }
                    }
                }
            }
            kq = dal.MyExecuteNonQuerys("Update ARHDR  Set CR_flag=0 WHERE statusflg=1  And trntyp = 'I' AND so = 1 And ARHDR.chanel=" + login_chanel + " and tranno In (Select InvNum From " + login_name + "AROVERINVOICE)", SVName, DBName);
            ds = dal.MyExecuteReader(ref err, "SELECT     Customer, InvNum, InvDate, InvAmt, Reason FROM         " + DBName + ".dbo.[" + login_name + "AROVERINVOICE]", CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string ApprovalCredit(string sitecode, string Tranno, string type, string chanel)
        {
            //shd;dhd;...
            DAL dal = new DAL();
            string err = "";
            string DBName = GetDatabaseName(sitecode, dal);
            string SVName = GetServerName(sitecode, dal);
            bool kq = false;
            string[] tr = Tranno.Split(';');
            try
            {
                for (int i = 0; i < tr.Length; i++)
                {
                    if (type == "credit")
                        kq = dal.MyExecuteNonQuerys("Update ARHDR Set CRApproval=1,CR_flag=1 Where chanel=" + chanel + " and trntyp='I' And tranno='" + tr[i] + "'", SVName, DBName);
                    if (type == "aproarprodef")
                        kq = dal.MyExecuteNonQuerys("Update ARPRODEF Set active=1 Where  Program_ID='" + tr[i] + "'", SVName, DBName);
                    if (type == "apcash")
                        kq = dal.MyExecuteNonQuerys("Update APCASHPAYMENT Set IsApproval=1 Where chanel=" + chanel + " and tranno='" + tr[i] + "'", SVName, DBName);
                    if (type == "arcash")
                        kq = dal.MyExecuteNonQuerys("Update ARCASHRECEIPT Set IsApproval=1 Where chanel=" + chanel + " and tranno='" + tr[i] + "'", SVName, DBName);
                    if (type == "A")
                        kq = dal.MyExecuteNonQuerys("Update INTXD Set IsApproval=1 Where chanel=" + chanel + " and docno='" + tr[i] + "' and trntyp='A'", SVName, DBName);
                    if (type == "M")
                        kq = dal.MyExecuteNonQuerys("Update INTXD Set IsApproval=1 Where chanel=" + chanel + " and docno='" + tr[i] + "' and trntyp='M'", SVName, DBName);
                    if (type == "L")
                        kq = dal.MyExecuteNonQuerys("Update INTXD Set IsApproval=1 Where chanel=" + chanel + " and docno='" + tr[i] + "' and trntyp='L'", SVName, DBName);
                    if (type == "F")
                        kq = dal.MyExecuteNonQuerys("Update INTXD Set IsApproval=1 Where chanel=" + chanel + " and docno='" + tr[i] + "' and trntyp='F'", SVName, DBName);
                    if (type == "AP")
                        kq = dal.MyExecuteNonQuerys("Update APHDR Set CRApproval=1 Where chanel=" + chanel + " and vouchno='" + tr[i] + "'", SVName, DBName);
                    if (type == "AR")
                        kq = dal.MyExecuteNonQuerys("Update ARHDR Set CRApproval=1 Where chanel=" + chanel + " and tranno='" + tr[i] + "' ", SVName, DBName);

                    if (kq)
                        err = err + tr[i] + ";";
                }
                return err;
            }
            catch
            {
                return "0";
            };
        }
        [WebMethod]
        public string ChangeArProAllowcation(string sitecode, string values, string type, string chanel)
        {
            //shd;dhd;...
            DAL dal = new DAL();
            string err = "";
            string DBName = GetDatabaseName(sitecode, dal);
            string SVName = GetServerName(sitecode, dal);
            bool kq = false;
            string[] tr = values.Split(';');
            try
            {
                for (int i = 0; i < tr.Length - 1; i += 2)
                {
                    if (tr[i + 1] == "-1")
                        kq = dal.MyExecuteNonQuerys("Update ARPROAllocate Set AllocateVal=null Where Program_ID='" + tr[i] + "' and chanel=" + chanel, SVName, DBName);
                    else
                        kq = dal.MyExecuteNonQuerys("Update ARPROAllocate Set AllocateVal=" + tr[i + 1] + " Where Program_ID='" + tr[i] + "' and chanel=" + chanel, SVName, DBName);

                }
                return err;
            }
            catch
            {
                return "0";
            };
        }
        [WebMethod]
        public DataSet GetARCASHRECEIPT(string sitecode, string chanel)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     tranno, Payer, docdate, payfor, paytot, IsPrint, trntyp FROM " + DBName + ".dbo.ARCASHRECEIPT WHERE    chanel=" + chanel + " and (post = 0) AND (IsApproval = 0)";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetAPCASHPAYMENT(string sitecode, string chanel)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT tranno, payer, docdate, payfor, paytot, trntyp FROM " + DBName + ".dbo.APCASHPAYMENT WHERE  chanel=" + chanel + " and   (post = 0) AND (IsApproval = 0)";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetARPRODEF(string sitecode, string type, string chanel)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     RIGHT([Closed Date], 4) + '-' + SUBSTRING([Closed Date], 4, 2) + '-' + LEFT([Closed Date], 2) AS ClosedDate FROM " + DBName + ".dbo.ARSALESPTYPE";
            string ClosedDate = dal.GetString(sql);
            if (type == "0")
                sql = "SELECT     ARPRODEF.Program_ID, ARPRODEF.Program_Des, ARPRODEF.[From], ARPRODEF.[To], case when AllocateVal is null then -1 else AllocateVal end as AllocateVal FROM " + DBName + ".dbo.ARPRODEF INNER JOIN " +
                  DBName + ".dbo.ARPROAllocate ON ARPRODEF.Program_ID = ARPROAllocate.Program_ID WHERE    ARPROAllocate.chanel=" + chanel + " and (ARPRODEF.[To] >= '" + ClosedDate + "')";
            else
                sql = "SELECT     ARPRODEF.Program_ID, ARPRODEF.Program_Des, ARPRODEF.[From], ARPRODEF.[To], ARPROAllocate.AllocateVal FROM " + DBName + ".dbo.ARPRODEF INNER JOIN " +
                  DBName + ".dbo.ARPROAllocate ON ARPRODEF.Program_ID = ARPROAllocate.Program_ID WHERE    ARPROAllocate.chanel=" + chanel + " and (ARPRODEF.[To] >= '" + ClosedDate + "') and active=0";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetAPHDR(string sitecode, string chanel, string type)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "";
            if (type == "D")
                sql = "SELECT     apv.vendorkey, apv.vendorname, aph.vouchno, aph.invdate, aph.vouchtotal FROM " + DBName + ".dbo.APHDR AS aph INNER JOIN  " + DBName + ".dbo.APVEND AS apv ON aph.vendorkey = apv.vendorkey WHERE  chanel=" + chanel + " and   (aph.trntyp = 'D') and CRApproval =0";
            else
                sql = "SELECT     apv.vendorkey, apv.vendorname, aph.vouchno, aph.invdate, aph.vouchtotal FROM " + DBName + ".dbo.APHDR AS aph INNER JOIN  " + DBName + ".dbo.APVEND AS apv ON aph.vendorkey = apv.vendorkey WHERE  chanel=" + chanel + " and   (aph.NoneItem = 1) and CRApproval =0";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetARHDR(string sitecode, string chanel, string type)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "";
            if (type == "D")
                sql = "SELECT     apv.vendorkey, apv.vendorname, aph.vouchno, aph.invdate, aph.vouchtotal FROM " + DBName + ".dbo.APHDR AS aph INNER JOIN  " + DBName + ".dbo.APVEND AS apv ON aph.vendorkey = apv.vendorkey WHERE  chanel=" + chanel + " and   (aph.trntyp = 'D') and CRApproval =0";
            if (type == "A")
                sql = "SELECT     apv.vendorkey, apv.vendorname, aph.vouchno, aph.invdate, aph.vouchtotal FROM " + DBName + ".dbo.APHDR AS aph INNER JOIN  " + DBName + ".dbo.APVEND AS apv ON aph.vendorkey = apv.vendorkey WHERE  chanel=" + chanel + " and   (aph.NoneItem = 1) and CRApproval =0";
            if (type == "I")
                sql = "SELECT     ar.customer_key AS vendorkey, TC.customername + '_' + TC.address AS vendorname, ar.tranno AS vouchno, ar.invdate, ar.doctot AS vouchtotal FROM  ARHDR AS ar INNER JOIN TCARCUST AS TC ON ar.customer_key = TC.customerkey WHERE     (ar.CRApproval = 0) AND (ar.NoneItem = 1)";

            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string ResetGPS(string sitecode, string Cusomerkey)
        {
            //shd;dhd;...
            DAL dal = new DAL();
            string err = "";
            string DBName = GetDatabaseName(sitecode, dal);
            try
            {
                string sql = "Update " + DBName + ".dbo.TCARCUST Set Long=0 ,Lat=0 where Customerkey='" + Cusomerkey + "'";
                bool kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref err);
                if (kq)
                    return "1";
                else
                    return "0";
            }
            catch
            {
                return "0";
            };
        }
        [WebMethod]
        public DataSet GetINTXDProval(string sitecode, string type)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     itx.docno, itx.docdate, itx.itemkey, inm.description,itx.stockqty, itx.stocprice, inm.lastcost, inm.price, itx.trntyp,itx.Recuser, " +
                        "CASE WHEN location IS NULL THEN '' ELSE location END AS location, CASE WHEN tolocation IS NULL THEN '' ELSE tolocation END AS tolocation" +
                        " FROM " + DBName + ".dbo.INTXD AS itx INNER JOIN  " + DBName + ".dbo.INMAST AS inm ON itx.itemkey = inm.itemkey WHERE     (itx.IsApproval = 0)";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet VSBSITEMORDER(string sitecode, string Chanel)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     OrderName, Itemkey, SortOrder, Chanel FROM VSBSITEMORDER WHERE  (Chanel = " + Chanel + ")";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string InputIRA(string sitecode, string Chanel, string IRA)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string sql = "";
            string error = "";
            string[] tr = IRA.Split(';');
            try
            {
                for (int i = 1; i < tr.Length - 2; i += 2)
                {

                }
            }
            catch
            {

            }
            return "";
        }
        [WebMethod]
        public DataSet GetInlocd(string sitecode)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     location, description, loctype, IRACheck FROM " + DBName + ".dbo.INLOCD WHERE     (loctype = 'CH')";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetClosedate(string sitecode, string chanel)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     [Closed Date] as ClosedDate FROM  " + DBName + ".dbo.ARSALESPTYPE WHERE     (Salespcode = " + chanel + ")";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string GetVSBSITEMORDER(string sitecode)
        {
            string VSBSITEMORDER = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT  Distinct  OrderName FROM  " + DBName + ".dbo.VSBSITEMORDER WHERE     (OrderName <> '')";
            string error = "";
            DataSet ds1 = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            for (int y = 0; y < ds1.Tables[0].Rows.Count; y++)
            {
                VSBSITEMORDER = VSBSITEMORDER + ds1.Tables[0].Rows[y]["OrderName"].ToString() + ";";
                sql = "SELECT     OrderName, Itemkey, SortOrder FROM  " + DBName + ".dbo.VSBSITEMORDER WHERE     (OrderName <> '') and OrderName='" + ds1.Tables[0].Rows[y]["OrderName"].ToString() + "'";
                ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    VSBSITEMORDER = VSBSITEMORDER + ds.Tables[0].Rows[i]["Itemkey"].ToString() + ";";
                    VSBSITEMORDER = VSBSITEMORDER + ds.Tables[0].Rows[i]["SortOrder"].ToString();
                    if (i < ds.Tables[0].Rows.Count - 1)
                        VSBSITEMORDER = VSBSITEMORDER + ";";
                }
                if (y < ds1.Tables[0].Rows.Count - 1)
                    VSBSITEMORDER = VSBSITEMORDER + "@";
            }
            return VSBSITEMORDER;
        }
        [WebMethod]
        public string UploadReturnSerial(string sitecode, string salespkey, string values)
        {
            string fl = "0";
            string err = "", sql = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            sql = "SELECT     SaleLocation FROM  " + DBName + ".dbo.ARSALESP WHERE     (salesp_key = '" + salespkey + "')";
            string Prloc = dal.MyExecuteReaders(ref err, sql, CommandType.Text);
            sql = "insert into " + DBName + ".dbo.RETURNSERIAL( Location, invdate, ReturnValue, date) values('" + Prloc + "','" + DateTime.Now.ToString("dd/MM/yyyy") + "','" + values + "',getdate())";
            bool kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref err);
            try
            {
                sql = "Update " + DBName + ".dbo.INLOCSERIAL Set Status=0 ,Qty=1, Indate='', tranno='',customerkey='' where location='" + Prloc + "' and Indate='" + DateTime.Now.ToString("dd/MM/yyyy") + "'";
                kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref err);
                string[] data = values.Split('@');
                for (int i = 0; i < data.Length; i++)
                {
                    if (data[i] != "")
                    {
                        string[] serial = data[i].Split('.');
                        if (serial[3] == "0")
                        {
                            sql = "Update " + DBName + ".dbo.INLOCSERIAL Set  Status=1,customerkey='',tranno='',Indate='" + DateTime.Now.ToString("dd/MM/yyyy") + "' where itemkey='" + serial[0] + "' and " +
                                " location='" + Prloc + "'  and Serial>='" + serial[1] + "' and Serial<='" + serial[2] + "'";
                        }
                        else
                        {
                            sql = "Update " + DBName + ".dbo.INLOCSERIAL Set  Status=1,customerkey='',tranno='',Indate='" + DateTime.Now.ToString("dd/MM/yyyy") + "' where itemkey='" + serial[0] + "' and " +
                               " location='" + Prloc + "'  and Serial>='" + serial[1] + "' and Serial<='" + serial[2] + "'";
                            //sql = "Update " + DBName + ".dbo.INLOCSERIAL Set  Status=1,customerkey='',Indate='16/07/2013' where itemkey='" + serial[0] + "' and " +
                            //    " location='" + Prloc + "'  and Serial>='" + serial[1] + "' and Serial<='" + serial[2] + "'";                            

                        }
                        kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref err);
                    }
                }
                DataSet ds = new DataSet();
                sql = "SELECT     al.tranno, al.itemkey, ah.customer_key, ah.invdate, ah.Salespkey, al.location,al.shipqty FROM " + DBName + ".dbo.ARLIN AS al INNER JOIN " + DBName + ".dbo.ARHDR AS ah ON al.tranno = ah.tranno AND al.trntyp = ah.trntyp" +
                    " WHERE     (ah.Salespkey = '" + salespkey + "') AND (al.location = '" + Prloc + "') AND (ah.invdate = '" + DateTime.Now.ToString("dd/MM/yyyy") + "') and al.itemkey in(SELECT     itemkey FROM " + DBName + ".dbo.INMAST WHERE     (SaleSerial = 1))";
                //sql = "SELECT     al.tranno, al.itemkey, ah.customer_key, ah.invdate, ah.Salespkey, al.location,al.shipqty FROM " + DBName + ".dbo.ARLIN AS al INNER JOIN " + DBName + ".dbo.ARHDR AS ah ON al.tranno = ah.tranno AND al.trntyp = ah.trntyp" +
                //   " WHERE     (ah.Salespkey = '" + salespkey + "') AND (al.location = '" + Prloc + "') AND (ah.invdate = '16/07/2013') and al.itemkey in(SELECT     itemkey FROM " + DBName + ".dbo.INMAST WHERE     (SaleSerial = 1))";

                ds = dal.MyExecuteReader(ref err, sql, CommandType.Text);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    for (int y = 0; y < int.Parse(ds.Tables[0].Rows[i]["shipqty"].ToString()); y++)
                    {
                        sql = "SELECT     TOP (1) Serial FROM " + DBName + ".dbo.INLOCSERIAL  WHERE     (Status = 0) AND (tranno = '') AND (location = '" + Prloc + "') AND (itemkey = '" + ds.Tables[0].Rows[i]["itemkey"].ToString() + "') OR " +
                            " (Status = 0) AND (tranno IS NULL) AND (location = '" + Prloc + "') AND (itemkey = '" + ds.Tables[0].Rows[i]["itemkey"].ToString() + "') ORDER BY Serial";
                        string seri = dal.MyExecuteReaders(ref err, sql, CommandType.Text);
                        //sql = "Update " + DBName + ".dbo.INLOCSERIAL Set  Status=1,tranno='" + ds.Tables[0].Rows[i]["tranno"].ToString() + "',customerkey='" + ds.Tables[0].Rows[i]["customer_key"].ToString() + "',Indate='16/07/2013' where Serial='" + seri + "'";
                        sql = "Update " + DBName + ".dbo.INLOCSERIAL Set  Status=1,Qty=0,tranno='" + ds.Tables[0].Rows[i]["tranno"].ToString() + "',customerkey='" + ds.Tables[0].Rows[i]["customer_key"].ToString() + "',Indate='" + DateTime.Now.ToString("dd/MM/yyyy") + "' where Serial='" + seri + "'";

                        kq = dal.MyExecuteNonQuery(sql, CommandType.Text, ref err);
                    }
                }
                sql = "SELECT     TOP (1) Serial FROM " + DBName + ".dbo.INLOCSERIAL  WHERE     (Status = 0) AND (tranno = '') AND (location = '" + Prloc + "')  OR " +
                            " (Status = 0) AND (tranno IS NULL) AND (location = '" + Prloc + "')  ORDER BY Serial";
                string ser = dal.MyExecuteReaders(ref err, sql, CommandType.Text);
                if (ser == "")
                    fl = "1";
                else
                    fl = "0";
            }
            catch
            {
                fl = "0";
            }
            return fl;
        }
        [WebMethod]
        public DataSet GetReaderwhithwhere(string sitecode, string chanel, string Table, string where)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT    * FROM  " + DBName + ".dbo.[" + Table + "] where " + where;
            if (sitecode == "")
                sql = "SELECT    * FROM  dbo.[" + Table + "] where " + where;
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetReaderwhithwherefield(string sitecode, string chanel, string Table, string where, string field)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string SvName = GetServerName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT   " + field + " FROM  [" + Table + "] where " + where;
            string error = "";
            if (sitecode == "")
                ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            else
                ds = dal.MyExecuteReader(ref error, sql, CommandType.Text, DBName, SvName);
            return ds;
        }
        [WebMethod]
        public DataSet GetReaderwhithoutewhere(string sitecode, string chanel, string Table)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT    * FROM  " + DBName + ".dbo." + Table;
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }

        [WebMethod]
        public DataSet GetMobileOutlet(string sitecode, string chanel, string Table, string salespkey)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            string where = "";
            string dayoffweek = DateTime.Today.DayOfWeek.ToString();
            if (dayoffweek.ToUpper() == "MONDAY")
                where = "  WHERE Customerkey IN(SELECT     customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE     (MCP2 = 1) AND (salepersonkey = '" + salespkey + "'))";
            if (dayoffweek.ToUpper() == "TUESDAY")
                where = "  WHERE Customerkey IN(SELECT     customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE     (MCP3 = 1) AND (salepersonkey = '" + salespkey + "'))";
            if (dayoffweek.ToUpper() == "WEDNESDAY")
                where = "  WHERE Customerkey IN(SELECT     customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE     (MCP4 = 1) AND (salepersonkey = '" + salespkey + "'))";
            if (dayoffweek.ToUpper() == "THURSDAY")
                where = "  WHERE Customerkey IN(SELECT     customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE     (MCP5 = 1) AND (salepersonkey = '" + salespkey + "'))";
            if (dayoffweek.ToUpper() == "FRIDAY")
                where = "  WHERE Customerkey IN(SELECT     customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE     (MCP6 = 1) AND (salepersonkey = '" + salespkey + "'))";
            if (dayoffweek.ToUpper() == "SATURDAY")
                where = "  WHERE Customerkey IN(SELECT     customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE     (MCP7 = 1) AND (salepersonkey = '" + salespkey + "'))";
            if (dayoffweek.ToUpper() == "SUNDAY")
                where = "  WHERE Customerkey IN(SELECT     customerkey FROM  " + DBName + ".dbo.TCARCUST WHERE     (MCP2 = 1) AND (salepersonkey = '" + salespkey + "'))";

            DataSet ds = new DataSet();
            string sql = "SELECT    * FROM  " + DBName + ".dbo." + Table + where;
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }

        [WebMethod]
        public DataSet Offtake(string sitecode, string chanel, string Salespkey, string type)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "";
            if (type == "SKU")
                sql = "SELECT     SkuCode, OffDate, SUM(OffAmt) AS OffAmt FROM  " + DBName + ".dbo.MobileDOOR WHERE     (CustClass <> 'PR') AND (SalespKey = '" + Salespkey + "') GROUP BY SkuCode, OffDate";
            if (type == "Cust")
                sql = "SELECT     CustKey, OffDate, SUM(OffAmt) AS OffAmt FROM  " + DBName + ".dbo.MobileDOOR WHERE     (CustClass <> 'PR') AND (SalespKey = '" + Salespkey + "') GROUP BY CustKey, OffDate";
            if (type == "DOOR")
                sql = "SELECT     CustKey,SkuCode, OffAmt, OffQty,0 as RetQty,0 AS RetAmt, OffDate, CustClass,'0' as Program_ID FROM  " + DBName + ".dbo.MobileDOOR WHERE     (SalespKey = '" + Salespkey + "')";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetMonthlyForcus(string sitecode, string chanel, string Salespkey)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     SKUCode, Timing, ForcusName, chanel, TeamID, ForcusType FROM " + DBName + ".dbo.MonthlyForcus AS M WHERE     (SKUCode IN (SELECT     itemkey FROM " + DBName + ".dbo.INLOC AS INL)) OR SKUCode IN (SELECT     SkuCode FROM  " + DBName + ".dbo.MobileDOOR AS DR)";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetVolumeHeader(string sitecode, string chanel, string Salespkey)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     VL.VolumeID, VL.Description, VL.Timing, VLD.SKUCode FROM  " + DBName + ".dbo.VolumeHeader AS VL INNER JOIN  " + DBName + ".dbo.VolumeHeaderDetail AS VLD ON VL.VolumeID = VLD.VolumeID WHERE     (VLD.SKUCode IN (SELECT     itemkey  FROM  " + DBName + ".dbo.INLOC)) OR  (VLD.SKUCode IN (SELECT     SkuCode  FROM  " + DBName + ".dbo.MobileDOOR WHERE      (CustClass <> 'pr')))";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetDistributionHeader(string sitecode, string chanel, string Salespkey, string type)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     DistributionID, Description, Timing FROM  " + DBName + ".dbo.DistributionHeader";
            if (type == "Detail")
                sql = "SELECT     DIS.DistributionID, DIS.Description, DIS.Timing, DISD.SKUCode FROM  " + DBName + ".dbo.DistributionHeader AS DIS INNER JOIN  " + DBName + ".dbo.DistributionHeaderDetail AS DISD ON DIS.DistributionID = DISD.DistributionID WHERE (DISD.SKUCode IN (SELECT SkuCode FROM " + DBName + ".dbo.MobileDOOR  WHERE (CustClass <> 'pr'))) OR (DISD.SKUCode IN (SELECT  itemkey  FROM " + DBName + ".dbo.INLOC))";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public DataSet GetMonthlyTargetbyDSR(string sitecode, string chanel, string Salespkey)
        {
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            DataSet ds = new DataSet();
            string sql = "SELECT     [Values], Vtype, VtypeDes, Timing, chanel, TeamID, SaleMan FROM    " + DBName + ".dbo.MobileMonthlyTargetbyDSR WHERE     (SaleMan = '" + Salespkey + "')";
            string error = "";
            ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
            return ds;
        }
        [WebMethod]
        public string UploadInlocOutlet(string InlocOutlet, string sitecode, string salespkey)
        {
            //cau truc sitecode.manv@makh.masp.sl...@makh.sl.mkh.sl...
            string error = "", sql = "";
            DAL dal = new DAL();
            string DBName = GetDatabaseName(sitecode, dal);
            bool fl = false;

            string[] InlocOutletDetail = InlocOutlet.Split('@');
            try
            {
                if (InlocOutletDetail.Length >= 1)
                {
                    for (int i = 0; i < InlocOutletDetail.Length; i++)
                    {
                        string[] InlocOutletDetailSku = InlocOutletDetail[i].Split('.');
                        if (InlocOutletDetailSku.Length > 1)
                        {
                            sql = "delete " + DBName + ".dbo.INLOCOUTLET where Customerkey='" + InlocOutletDetailSku[0] + "' and Salesp_key='" + salespkey + "' and Inlocdate='" + DateTime.Today.ToString("dd/MM/yyyy") + "'";
                            fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            for (int y = 1; y < InlocOutletDetailSku.Length - 1; y += 2)
                            {
                                sql = "Insert into " + DBName + ".dbo.INLOCOUTLET(Salesp_key, Customerkey, Inlocdate, Itemkey, Qty)" +
                                    " values('" + salespkey + "','" + InlocOutletDetailSku[0] + "','" + DateTime.Today.ToString("dd/MM/yyyy") + "','" + InlocOutletDetailSku[y] + "','" + InlocOutletDetailSku[y + 1] + "')";
                                fl = dal.MyExecuteNonQuerys(sql, CommandType.Text, ref error);
                            }
                        }
                    }
                }
                return "1";
            }
            catch
            {
                return "0";
            }
        }
        [WebMethod]
        public string UploadCustomerImage(string SiteCode, string Salesp, string Type, string ShopCode, string ImageData, string Recdate)
        {
            string date = DateTime.Now.ToString("dd/MM/yyyy");
            DAL dal = new DAL();
            bool fl = false;
            string error = "", sql = "";
            string DBName = GetDatabaseName(SiteCode, dal);
            string SvName = GetServerName(SiteCode, dal);
            sql = "delete OuteletImages where SiteCode='" + SiteCode + "' and ShopCode='" + ShopCode + "' and Type=" + Type;
            dal.MyExecuteNonQuerys(sql, SvName, DBName);
            //if (type == "2")
            {
                byte[] imageBytes = Convert.FromBase64String(ImageData);
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Parameters.Add(new SqlParameter("@SiteCode", SiteCode));
                    cmd.Parameters.Add(new SqlParameter("@ShopCode", ShopCode));
                    cmd.Parameters.Add(new SqlParameter("@Salesp", Salesp));
                    cmd.Parameters.Add(new SqlParameter("@ImageData", imageBytes));
                    cmd.Parameters.Add(new SqlParameter("@CreateDate", Recdate));
                    cmd.Parameters.Add(new SqlParameter("@Type", Type));
                    cmd.CommandText = "insert into OuteletImages(SiteCode, ShopCode, Salesp, ImageData, CreateDate, Type)" +
                        " values(@SiteCode,@ShopCode,@Salesp,@ImageData,@CreateDate, @Type)";
                    string sMyConnectionString = @"Data Source=" + SvName + ";User Id=admin;Password=baccamay;Initial Catalog = " + DBName;

                    using (SqlConnection con = new SqlConnection(sMyConnectionString))
                    {
                        cmd.Connection = con;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                    cmd.CommandText = "insert into OuteletImagesh(SiteCode, ShopCode, Salesp, ImageData, CreateDate, Type)" +
                       " values(@SiteCode,@ShopCode,@Salesp,@ImageData,@CreateDate, @Type)";
                    using (SqlConnection con = new SqlConnection(sMyConnectionString))
                    {
                        cmd.Connection = con;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            if (fl)
                return "1";
            else
                return "0";
        }
        [WebMethod]
        public string DowloadCustomerImage(string Sitecode, string Customerkey, string Imagetype)
        {
            string version = "-1";
            try
            {
                string error = "", sql = "";
                DAL dal = new DAL();
                string DBName = GetDatabaseName(Sitecode, dal);
                sql = "SELECT     TOP (1) ImageData FROM  " + DBName + ".dbo.OuteletImages WHERE     (Type = N'" + Imagetype + "') AND (ShopCode = N'" + Customerkey + "') ORDER BY CreateDate DESC";
                DataSet ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    byte[] encodedDataAsBytes = (byte[])ds.Tables[0].Rows[0]["ImageData"];
                    version = System.Convert.ToBase64String(encodedDataAsBytes);
                }
                else
                    version = "-1";
            }
            catch
            {
                version = "-1";
            }
            return version;
        }
        [WebMethod]
        public string PostIssue(string SiteCode, string Salesp, string ImageData)
        {
            DAL dal = new DAL();
            bool fl = false;
            DateTime date = DateTime.Now;
            {
                byte[] imageBytes = Convert.FromBase64String(ImageData);
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Parameters.Add(new SqlParameter("@SiteCode", SiteCode));
                    cmd.Parameters.Add(new SqlParameter("@Salesp", Salesp));
                    cmd.Parameters.Add(new SqlParameter("@ImageData", imageBytes));
                    cmd.Parameters.Add(new SqlParameter("@CreateDate", date));
                    cmd.CommandText = "insert into ISSUE(SiteCode, Salesp,ImageData, CreateDate)" +
                        " values(@SiteCode,@Salesp,@ImageData,@CreateDate)";
                    string sMyConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString + " uid=admin; pwd=baccamay";

                    using (SqlConnection con = new SqlConnection(sMyConnectionString))
                    {
                        cmd.Connection = con;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                    fl = true;
                }
            }
            if (fl)
                return "1";
            else
                return "0";
        }
        [WebMethod]
        public string ApprovalDistributorOrder(string sitecode, string usercode, string Order)
        {
            try
            {
                DAL dal = new DAL();
                string SvName = GetServerName(sitecode, dal);
                ApprovalDistributorOrder appr = new ApprovalDistributorOrder();
                string filepath = @"D:\\DistributorOrder\\Order\\";
                string filename = "EDI_" + sitecode + "_" + DateTime.Now.ToString("yyMMdd_HHmm");
                appr.WriteFile(usercode, filepath + filename + ".txt", DateTime.Now.ToString("dd/MM/yyyy"), Order, SvName);
                appr.DeleteFile(filepath + filename + ".zip");
                appr.Zipfile(filepath + filename + ".zip", "ediKCV14#", filepath + filename + ".txt");
                string sql = "Update Distributor_Order_Full set Approval=1 where RIGHT(InvNo, 13) in(" + Order + ")";
                bool fl = dal.MyExecuteNonQuerys(sql);
                sql = "Update  Distributor_OrderFull set Approval=1 where Week in(" + Order + ")";
                fl = dal.MyExecuteNonQuerys(sql, SvName, "CDCReports");
                return "1";
            }
            catch (Exception ex)
            {
                string resu = ex.Message.ToString();
                return "0";
            }
        }
        [WebMethod]
        public string ApprovalCustomerInfor(string sitecode, string usercode, string CustomerInfor)
        {
            string error = "", sql = "";
            DAL dal = new DAL();
            bool fl = false;
            string DBName = GetDatabaseName(sitecode, dal);
            string SvName = GetServerName(sitecode, dal);
            string[] CustomerInfordetail = CustomerInfor.Split('@');
            try
            {
                if (CustomerInfordetail.Length >= 1)
                {
                    for (int i = 1; i < CustomerInfordetail.Length; i++)
                    {
                        string[] customer = CustomerInfordetail[i].Split('#');
                        if (customer.Length > 2)
                        {
                            sql = "delete APPROVALCUSTOMERINFOR where Customerkey='" + customer[0] + "' and Type='" + customer[1] + "' and [key]='" + customer[2] + "'";
                            fl = dal.MyExecuteNonQuerys(sql, SvName, DBName);
                            sql = "insert into APPROVALCUSTOMERINFOR( Customerkey, Type, [key], [Values], Comment)values('" + customer[0] + "','" + customer[1] + "','" + customer[2] + "'," + customer[3] + ",'" + customer[4] + "')";
                            fl = dal.MyExecuteNonQuerys(sql, SvName, DBName);
                            sql = "insert into APPROVALCUSTOMERINFORH( Customerkey, Type, [key], [Values], Comment,Recdate)values('" + customer[0] + "','" + customer[1] + "','" + customer[2] + "'," + customer[3] + ",'" + customer[4] + "',getdate())";
                            fl = dal.MyExecuteNonQuerys(sql, SvName, DBName);
                        }

                    }
                    sql = "update ARCUST set ApprovalGPS=1 from APPROVALCUSTOMERINFOR where Customerkey=customer_key and Type='GPS' and [Values]=1";
                    fl = dal.MyExecuteNonQuerys(sql, SvName, DBName);
                    sql = "update ARCUST set IsApproval=1 from APPROVALCUSTOMERINFOR where Customerkey=customer_key and Type='INF' and [Values]=1";
                    fl = dal.MyExecuteNonQuerys(sql, SvName, DBName);
                    sql = "update OuteletImages set isApproval=1 from APPROVALCUSTOMERINFOR where Customerkey=ShopCode and APPROVALCUSTOMERINFOR.Type='IMG' and OuteletImages.Type=APPROVALCUSTOMERINFOR.[key] and [Values]=1";
                    fl = dal.MyExecuteNonQuerys(sql, SvName, DBName);

                }
                return "1";
            }
            catch
            {
                return "0";
            }
        }
        [WebMethod]
        public string DowloadImage(string Sitecode, string Tabale, string field, string Where)
        {
            string image = "-1";
            try
            {
                string error = "", sql = "";
                DAL dal = new DAL();
                string DBName = "";
                if (Sitecode != "")
                    DBName = GetDatabaseName(Sitecode, dal) + ".dbo.";
                sql = "SELECT     TOP (1) " + field + " FROM  " + DBName + Tabale + " WHERE    " + Where;
                DataSet ds = dal.MyExecuteReader(ref error, sql, CommandType.Text);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    byte[] encodedDataAsBytes = (byte[])ds.Tables[0].Rows[0][0];
                    image = System.Convert.ToBase64String(encodedDataAsBytes);
                }
                else
                    image = "-1";
            }
            catch
            {
                image = "-1";
            }
            return image;
        }
        
    }
}
